# -*- coding: utf-8 -*-
import json,time,sqlite3,MySQLdb,threading,telnetlib
from Queue import Queue
from pysnmp.entity.rfc3413.oneliner import cmdgen
# from pysnmp.proto import rfc1902
from pysnmp.proto.rfc1902 import ObjectName
# 把列表转成sql的where in语句中的()部分
def c2sql(x):
  if len(x)==1:
    return str(tuple(x))[:-2]+")"
  else:
    return str(tuple(x))

def getParentId(id,allrp):
  for x in allrp:
    if x[0]==id:
      return x[1]
def getRootId(id,allrp):
  root=pid=id
  while pid<>None:
    pid=getParentId(pid,allrp)
    if pid<>None:
      root=pid
  return root

def getNdIp(id,nd):
  for x in nd:
    if x[0]==id:
      return x[1]
# 从mysql的rnd表取ndip数据
def read_mysql():
  s=time.time()
  nd=[]
  allrp=[]
  rp=[]
  tree=[]
  conn=MySQLdb.connect(host="172.16.100.31",user="root",passwd="admin",db="rainbow_3_0")
  cursor=conn.cursor()
  cursor.execute("SELECT neid,dsCfgNwIpAddr FROM rnd")
  [nd.append(x) for x in cursor.fetchall()]
  cursor.execute("select neid,ne_neid from ne where neid in (select neid from rrp)")
  [allrp.append(x) for x in cursor.fetchall()]
  cursor.execute("select ne.neid,ne_neid,embedaddress,nename,rpcfgnwstatus from rrp,ne where (rpcfgnwhop=1 or rpcfgnwhop=3 or rpcfgnwhop=5) and ne.neid=rrp.neid")
  [rp.append(x) for x in cursor.fetchall()]

  cursor.close()
  conn.close()
  for x in rp:
    rpid=x[0]
    rootid=getRootId(x[1],allrp)
    oid=x[2]
    rpmac=x[3]
    ndip=getNdIp(rootid,nd)
    rpstatus=x[4]
    tree.append((rpid,rootid,ndip,oid,rpmac,rpstatus))
  return tree
  print time.time()-s
    

  
def do_write_sqlite3(bad,good):

  s=time.time()
  last_change_time=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
  l=[]
  l.extend(bad)
  l.extend(good)
  tmp=[]
  ids=[]
  oldnotalive=[]
  # oldalive=[]
  lbad=[]
  # lgood=[]
  [lbad.append(x[0]) for x in bad]
  # [lgood.append(x[0]) for x in good]
  [tmp.append(x[0]) for x in l]
  conn=sqlite3.connect(r'lkt.db3')
  conn.text_factory = str
  [ids.append(x[0]) for x in conn.execute("select neid from rrp").fetchall()]
  conn.execute("delete from rrp where neid in "+c2sql(set(ids)-set(tmp)))
  for x in tuple(set(tmp)-set(ids)):
    conn.execute("insert into rrp(neid) values(?)",(x,))
  [oldnotalive.append(x[0]) for x in conn.execute("select neid from rrp where bAlive=0").fetchall()]
  # [oldalive.append(x[0]) for x in conn.execute("select neid from rrp where bAlive=1").fetchall()]
  alive=list(set(oldnotalive)-set(lbad))
  print "alive:",alive
  notalive=list(set(lbad)-set(oldnotalive))
  print "notalive:",notalive
  conn.execute("update rrp set iSnmp=iSnmp+1,dtPoll=?",(last_change_time,))
  for x in l:
    sql="update rrp set ndid=?,ndip=?,sSubOid=?,sRpMac=?,bRpStatus=?,bAlive=? where neid=?"
    conn.execute(sql,(x[1],x[2],x[3],x[4],x[5],x[6],x[0]))
  for x in alive:
    sql="update rrp set dtLog=?,bAlive=1,iSnmp=1 where neid=?"
    conn.execute(sql,(last_change_time,x))
  for x in notalive:
    sql="update rrp set dtLog=?,bAlive=0,iSnmp=1 where neid=?"
    conn.execute(sql,(last_change_time,x))
  conn.commit()
  conn.close()
  print "write_sqlite3:",time.time()-s

  
  
  
  
def write_sqlite3(bad,good):
  write_success=False
  while not write_success:
    try:
      do_write_sqlite3(bad,good)
      write_success=True
    except sqlite3.OperationalError,e:
      time.sleep(1)
    
  
  

def getsnmp(ndip,oid):
  errorIndication, errorStatus, errorIndex, varBinds = cmdgen.CommandGenerator().getCmd(cmdgen.CommunityData(ndip, 'public'),cmdgen.UdpTransportTarget((ndip, 161)),oid)
  if errorIndication:
    # print errorIndication
    snmp=None
  else:
    if errorStatus:
      # print '%s at %s\n' % (
        # errorStatus.prettyPrint(),
        # errorIndex and varBinds[int(errorIndex)-1] or '?'
        # )
      snmp=None
    else:
      # print varBinds
      for name, val in varBinds:
        
        # print '%s = %s' % (name.prettyPrint(), rfc1902.OctetString.prettyPrint(val))
        tmp=['%x' % ord(x) for x in val]
        snmp=[]
        for x in tmp:
          if len(x)==1:
            snmp.append("0"+x)
          elif len(x)==2:
            snmp.append(x)
        snmp='-'.join(snmp)
        # print snmp
        # snmp='-'.join(['%x' % ord(x) for x in snmp])
  return snmp

def getOid(soid):
  moid='1.3.6.1.4.1.5959.1.2.1.2.1.4'
  return ObjectName( moid+soid )
  
    
#具体的处理函数，负责处理单个任务
def SNMP_Poll(rps):
  s=time.time()
  bad=[]
  good=[]
  for x in rps:
    x=list(x)
    rpmac=None
    alive=0
    oid=getOid(x[3])
    # print oid
    rpmac=getsnmp(x[2],oid)
    if rpmac:
      if rpmac.upper()<>x[4]:
        print x,rpmac
      else:
        alive=1
    else:
      print x
    x.append(alive)
    if alive==0:
      bad.append(x)
    else:
      good.append(x)
    # rp=list(rps)
    # rp.append(alive)
    # print rp
  print time.time()-s
  return bad,good

while True:
  config = json.load(open("config.txt",'r'))
  delaytime=time.time()
  rps=read_mysql()
  print time.time()-delaytime
  print "starting poll..."
  bad,good=SNMP_Poll(rps)
  print "starting write sqlite3..."
  write_sqlite3(bad,good)
  delaytime=config["RPTest_Poll_Time"]-(time.time()-delaytime)
  if delaytime<=0:
    delaytime=config["RPTest_Poll_Time"]
  print "sleep time",delaytime
  time.sleep(delaytime)
